"""
历史调度过度通用处理
"""

from openpyxl import load_workbook
import pandas as pd
from io import BytesIO
from nlp import find_codec
from utils.request_utils import request_get
from parser.excel_parser import SheetResult, ExcelParser
from config.common import OSS_ACCESS_URL, HISTORY_SCHEDULING_FIELD_MAPPING, HISTORY_SCHEDULING_FILTER_CHARACTER
import json
from datetime import datetime, time


def parse_oss_excel(excel_path):
    excel_url = f"{OSS_ACCESS_URL}{excel_path}"
    excel_bytes: BytesIO = request_get(excel_url)
    if excel_bytes is not None:
        parser = ExcelParser()
        res: list[SheetResult] = parser(excel_bytes)
        return do_parse(res)


def do_parse(res: list[SheetResult]):
    result = []
    if not res or len(res) == 0:
        return result

    for sheet_result in res:
        rows = sheet_result.rows
        if not rows:
            continue

        # 获取表头，第二行
        titles = list(rows[1][1])
        if (not titles
                or (not titles[0] or not str(titles[0]).strip().find('日期') >= 0)
                or (not titles[1] or not str(titles[1]).strip().find('调度次数') >= 0)
                or (not titles[2] or not str(titles[2]).strip().find('孔数') >= 0)
        ):
            continue

        # 表头处理，补全最后一列
        if str(titles[len(titles) - 1]) == 'nan':
            titles[len(titles) - 1] = str(titles[len(titles) - 2]) + '_2'

        # 遍历工作表中的每一行
        for row in rows[2:]:
            row = row[1]
            row_data = {}
            for i, title in enumerate(titles):
                title = str(title).strip()
                val = row[i]

                if isinstance(row[i], float):
                    val = str(round(row[i], 2))
                elif isinstance(row[i], datetime):
                    val = row[i].strftime('%Y-%m-%d')
                elif isinstance(row[i], time):
                    val = row[i].strftime('%H:%M')

                # 处理非数字返回NaN的问题
                val = str(val)
                if val == 'nan':
                    val = ''

                field_name = HISTORY_SCHEDULING_FIELD_MAPPING.get(title)
                if field_name is not None:
                    row_data[field_name] = str(val)

            scheduling_date = row_data['scheduling_date']
            # 过滤掉最后一行统计数据
            if scheduling_date in HISTORY_SCHEDULING_FILTER_CHARACTER:
                continue

            # 过滤无效行
            if all(not s for s in (scheduling_date, row_data['scheduling_count'], row_data['hole_count'])):
                continue

            # 同一日期，调用多次，每次调度孔数不一样的情况下，则赋值上一行的日期作为当前行的日期
            if not scheduling_date:
                row_data['scheduling_date'] = result[-1]['scheduling_date']

            # print(json.dumps(row_data, ensure_ascii=False))
            result.append(row_data)

        return result


if __name__ == "__main__":
    # excel_path = 'knowledge-base/2024-12-11/2015年1月流量总表_f607bc784d1de713.xls'
    # parse_oss_excel(excel_path)
    # excel_path = 'knowledge-base/2024-12-11/2015年2月流量总表_df02838c236fbd03.xls'
    # parse_oss_excel(excel_path)
    excel_path = 'knowledge-base/2024-12-12/2015年4月流量总表_6df4da8132108b5b.xls'
    parse_oss_excel(excel_path)
